<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
	<meta name="theme-color" content="#33474d">
	<title>失落的乐章</title>
	<link rel="stylesheet" href="/css/style.css" />
	
      <link rel="alternate" href="/atom.xml" title="失落的乐章" type="application/atom+xml">
    
</head>

<body>

	<header class="header">
		<nav class="header__nav">
			
				<a href="/archives" class="header__link">Archive</a>
			
				<a href="/tags" class="header__link">Tags</a>
			
				<a href="/atom.xml" class="header__link">RSS</a>
			
		</nav>
		<h1 class="header__title"><a href="/">失落的乐章</a></h1>
		<h2 class="header__subtitle">技术面前，永远都是学生。</h2>
	</header>

	<main>
		
	<span class="different-posts different-posts_earlier">📖 <a href="/page/29">earlier posts</a> 📖</span>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/16. Mysql binlog 日志的三种模式/">Mysql binlog 日志的三种模式</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Row Level：日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了。所以row level的日志内容会非常清楚的记录下每一行数据修 改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或function，以及 trigger的调 用和触发无法被正确复制的问题。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;缺点：row level下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容，比如有这样一条update语句：update product set owner_member_id = ‘b’ where owner_member_id = ‘a’，执行之后，日志中记录的不是这条update语句所对应额事件(MySQL以事件的形式来记录bin-log日志)，而是这条语句所更新的每一条记录的变化情况，这样就记  录成很多条记录被更新的很多个事件。自然，bin-log日志的量就会很大。尤其是当执行alter table之类的语句的时候，产生的日志量是惊人的。因为MySQL对于alter table之类的表结构变更语句的处理 方式是整个表的每一条记录都需要变动，实际上就是重建了整个表。那么该表的每一条记录都会被记 录到日志中。</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Statement Level:每一条会修改数据的sql都会记录到 master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能。因为他只需要记录在Master上所执行的语句的细节，以及执行语句时候的上下文的信息。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;缺点：由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。另外就是，由于MySQL现在发展比较快，很多的新功能不断的加入，使MySQL得复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement level下，目前已经发现的就有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能真确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row level是基于每一行来记录的变化，所以不会出现类似的问题。</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;Mixed，实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。新版本中的Statment level还是和以前一样，仅仅记录执行的语句。而新版本的MySQL中队row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。</p>
<hr>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在配置文件中参数如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">log</span>-bin=mysql-bin</div><div class="line"><span class="comment">#binlog_format=”STATEMENT”</span></div><div class="line"><span class="comment">#binlog_format=”ROW”</span></div><div class="line">binlog_format=”MIXED”</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;运行时在线修改：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">mysql&gt; SET SESSION binlog_format = ‘STATEMENT’;</div><div class="line">mysql&gt; SET SESSION binlog_format = ‘ROW’;</div><div class="line">mysql&gt; SET SESSION binlog_format = ‘MIXED’;</div><div class="line">mysql&gt; SET GLOBAL binlog_format = ‘STATEMENT’;</div><div class="line">mysql&gt; SET GLOBAL binlog_format = ‘ROW’;</div><div class="line">mysql&gt; SET GLOBAL binlog_format = ‘MIXED’;</div></pre></td></tr></table></figure>
	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/15. MySQL存储引擎MyISAM与InnoDB的优劣/">MySQL存储引擎MyISAM与InnoDB的优劣</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用MySQL当然会接触到MySQL的存储引擎，在新建数据库和新建数据表的时候都会看到。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MySQL默认的存储引擎是MyISAM，其他常用的就是InnoDB了。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;至于到底用哪种存储引擎比较好？这个问题是没有定论的，需要根据你的需求和环境来衡量。所以对这两种引擎的概念、原理、异同和各自的优劣点有了详细的了解之后，再根据自己的情况选择起来就容易多了。</p>
<table>
<thead>
<tr>
<th>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</th>
<th style="text-align:center">MyISAM</th>
<th style="text-align:center">InnoDB</th>
</tr>
</thead>
<tbody>
<tr>
<td>存储结构</td>
<td style="text-align:center">每张表被存放在三个文件： frm-表格定义      MYD(MYData)-数据文件    MYI(MYIndex)-索引文件</td>
<td style="text-align:center">所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB</td>
</tr>
<tr>
<td>存储空间</td>
<td style="text-align:center">MyISAM可被压缩，存储空间较小</td>
<td style="text-align:center">InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引</td>
</tr>
<tr>
<td>可移植性、备份及恢复</td>
<td style="text-align:center">由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作</td>
<td style="text-align:center">免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了事务安全不支持 每次查询具有原子性支持 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表</td>
</tr>
<tr>
<td>AUTO_INCREMENT</td>
<td style="text-align:center">MyISAM表可以和其他字段一起建立联合索引</td>
<td style="text-align:center">InnoDB中必须包含只有该字段的索引</td>
</tr>
<tr>
<td>SELECT</td>
<td style="text-align:center">MyISAM更优</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;</td>
</tr>
<tr>
<td>INSERT</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">InnoDB更优</td>
</tr>
<tr>
<td>UPDATE</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">InnoDB更优</td>
</tr>
<tr>
<td>DELETE</td>
<td style="text-align:center">&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;</td>
<td style="text-align:center">InnoDB更优 它不会重新建立表，而是一行一行的删除</td>
</tr>
<tr>
<td>COUNT without WHERE</td>
<td style="text-align:center">MyISAM更优。因为MyISAM保存了表的具体行数</td>
<td style="text-align:center">InnoDB没有保存表的具体行数，需要逐行扫描统计，就很慢了</td>
</tr>
<tr>
<td>COUNT with WHERE</td>
<td style="text-align:center">一样</td>
<td style="text-align:center">一样，InnoDB也会锁表</td>
</tr>
<tr>
<td>锁</td>
<td style="text-align:center">只支持表锁</td>
<td style="text-align:center">支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的</td>
</tr>
<tr>
<td>外键</td>
<td style="text-align:center">不支持</td>
<td style="text-align:center">支持</td>
</tr>
<tr>
<td>FULLTEXT全文索引</td>
<td style="text-align:center">支持</td>
<td style="text-align:center">不支持 可以通过使用Sphinx从InnoDB中获得全文索引，会慢一点</td>
</tr>
</tbody>
</table>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;总的来说，MyISAM和InnoDB各有优劣，各有各的使用环境。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;但是InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;我觉得使用InnoDB可以应对更为复杂的情况，特别是对并发的处理要比MyISAM高效。同时结合memcache也可以缓存SELECT来减少SELECT查询，从而提高整体性能。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/14. SQL—什么是事物？事物的特性有哪些？/">SQL—什么是事物？事物的特性有哪些？</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<h2 id="概念"><a href="#概念" class="headerlink" title="概念"></a>概念</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;例如：在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。</p>
<h2 id="特性"><a href="#特性" class="headerlink" title="特性"></a>特性</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;事务是恢复和并发控制的基本单位。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;事务应该具有4个属性：原子性、一致性、隔离性、持续性。这四个属性通常称为ACID特性。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/12. mysql备份与恢复/">mysql备份与恢复</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<h2 id="备份"><a href="#备份" class="headerlink" title="备份"></a>备份</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysqldump -uroot -pyanyi discuz &gt; /data/discuz.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;说明：-p跟密码，中间不要有空格，后边的discuz是database名字，mysqldump出来的都是一些sql语句，所以用重定向符号 &gt; 给定向到一个文件中。</p>
<h2 id="恢复"><a href="#恢复" class="headerlink" title="恢复"></a>恢复</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi discuz &lt; /data/discuz.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;说明：这个过程正好和备份是相对的，反向重定向。</p>
<h2 id="只备份一个表"><a href="#只备份一个表" class="headerlink" title="只备份一个表"></a>只备份一个表</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysqldump -uroot -pyanyi discuz pre_forum_post &gt; /data/post.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;说明：discuz为数据库名字，pre_forum_post为表的名字，恢复不用加表名</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi discuz &lt; /data/post.sql</span></div></pre></td></tr></table></figure>
<h2 id="备份时指定字符集"><a href="#备份时指定字符集" class="headerlink" title="备份时指定字符集"></a>备份时指定字符集</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysqldump -uroot -pyanyi --default-character-set=gbk discuz pre_forum_post &gt; /data/post.sql</span></div></pre></td></tr></table></figure>
<h2 id="恢复也指定字符集"><a href="#恢复也指定字符集" class="headerlink" title="恢复也指定字符集"></a>恢复也指定字符集</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi --default-character-set=gbk discuz &lt; /data/post.sql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;说明：指定字符集的目的是为了避免有的建表sql中并没有指定字符集，而直接使用mysql默认字符集的情况，这样就会造成乱码。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/13. mysql常用操作/">mysql常用操作</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<h1 id="mysql常用操作"><a href="#mysql常用操作" class="headerlink" title="mysql常用操作"></a>mysql常用操作</h1><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;有一个图形管理mysql的工具叫phpmyadmin，而如何在命令行下面来管理和操作mysql。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show databases;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/01.png?raw=true" alt=""></figure></p>
<h2 id="查看某个库的表"><a href="#查看某个库的表" class="headerlink" title="查看某个库的表"></a>查看某个库的表</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; use mysql;</div><div class="line">mysql&gt; show tables;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/02.png?raw=true" alt=""></figure></p>
<h2 id="查看表的字段"><a href="#查看表的字段" class="headerlink" title="查看表的字段"></a>查看表的字段</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; desc pre_ucenter_vars;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/03.png?raw=true" alt=""></figure></p>
<h2 id="查看建表的语句"><a href="#查看建表的语句" class="headerlink" title="查看建表的语句"></a>查看建表的语句</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show create table pre_ucenter_vars\G;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/04.png?raw=true" alt=""></figure></p>
<h2 id="当前是哪个用户"><a href="#当前是哪个用户" class="headerlink" title="当前是哪个用户"></a>当前是哪个用户</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select user();</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/05.png?raw=true" alt=""></figure></p>
<h2 id="查看当前库"><a href="#查看当前库" class="headerlink" title="查看当前库"></a>查看当前库</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select database();</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/06.png?raw=true" alt=""></figure></p>
<h2 id="创建库"><a href="#创建库" class="headerlink" title="创建库"></a>创建库</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create database yanyi;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/07.png?raw=true" alt=""></figure></p>
<h2 id="创建表"><a href="#创建表" class="headerlink" title="创建表"></a>创建表</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; create table tb1 (`id` int(4), `name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;tb1 是表名；第一个字段是 <code>id</code>,格式是 int ，长度 4位；第二个字段 <code>name</code> 格式是 char 长度 40；指定 ENGINE 为 MyISAM ；字符集 DEFAULT CHARSET 为 gbk。</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/08.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;创建完成后查看</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/09.png?raw=true" alt=""></figure></p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/10.png?raw=true" alt=""></figure></p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/11.png?raw=true" alt=""></figure></p>
<h2 id="插入数据"><a href="#插入数据" class="headerlink" title="插入数据"></a>插入数据</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; insert into tb1 values (1,<span class="string">'yanyi'</span>);</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/12.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;还可以继续插入</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/13.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;也可以只插入一个字段</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; insert into tb1 (`id`) values (2);</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/14.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; insert into tb1 (`name`) values (<span class="string">'docker'</span>);</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/15.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; insert into tb1 (`name`,`id`) values (<span class="string">'redis'</span>,6);</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/16.png?raw=true" alt=""></figure></p>
<h2 id="查看数据库版本"><a href="#查看数据库版本" class="headerlink" title="查看数据库版本"></a>查看数据库版本</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select version();</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/17.png?raw=true" alt=""></figure></p>
<h2 id="查看-mysql-状态"><a href="#查看-mysql-状态" class="headerlink" title="查看 mysql 状态"></a>查看 mysql 状态</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show status;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/18.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;也可以用 like 过滤，% 通配</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show status like <span class="string">'%running'</span>;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/19.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show status like <span class="string">'%buffer%'</span>;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/20.png?raw=true" alt=""></figure></p>
<h2 id="修改-mysql-参数"><a href="#修改-mysql-参数" class="headerlink" title="修改 mysql 参数"></a>修改 mysql 参数</h2><p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/21.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; <span class="built_in">set</span> global max_connections=200;</div><div class="line">mysql&gt; show variables like <span class="string">'max_connections'</span>;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/22.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果记不住也可以用 % 通配</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show variables like <span class="string">'max_connec%'</span>;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/23.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;不过这些方法，重启就会变回以前配置，要永久生效需更改 /etc/my.cnf</p>
<h2 id="查看-mysql-队列"><a href="#查看-mysql-队列" class="headerlink" title="查看 mysql 队列"></a>查看 mysql 队列</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show processlist;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/24.png?raw=true" alt=""></figure></p>
<h2 id="查看-mysql-变量"><a href="#查看-mysql-变量" class="headerlink" title="查看 mysql 变量"></a>查看 mysql 变量</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; show variables;</div></pre></td></tr></table></figure>
<h2 id="创建普通用户并授权"><a href="#创建普通用户并授权" class="headerlink" title="创建普通用户并授权"></a>创建普通用户并授权</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div></pre></td><td class="code"><pre><div class="line">mysql&gt; grant all on *.* to user1 identified by <span class="string">'123456'</span>;</div><div class="line"></div><div class="line">mysql&gt; grant all on discuz.* to <span class="string">'user2'</span>@<span class="string">'192.168.0.%'</span> identified by <span class="string">'123456'</span>；</div><div class="line"></div><div class="line">mysql&gt; grant all on discuz.* to <span class="string">'user3'</span>@<span class="string">'%'</span> identifined by <span class="string">'123456'</span>;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;all 代表所有权限；<em>.</em>代表所有库里的所有表，discuz.*代表 discuz 库里的所有表；to 后边是用户名；@ 后边是客户端 ip ，192.168.0.%代表整个网段，% 代表通配，直接用 % 代替 ip 就表示所有网段；identfied by 后边是密码。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;用户创建完以后还没有即时生效，需要刷新下权限命令如下</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; flush privileges;</div></pre></td></tr></table></figure>
<h2 id="更改密码"><a href="#更改密码" class="headerlink" title="更改密码"></a>更改密码</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; update mysql.user <span class="built_in">set</span> password=password(<span class="string">"newpwd"</span>) <span class="built_in">where</span> user=<span class="string">'username'</span>;</div></pre></td></tr></table></figure>
<h2 id="查询"><a href="#查询" class="headerlink" title="查询"></a>查询</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select count(*) from mysql.user;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/25.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select * from mysql.db;select * from mysql.db <span class="built_in">where</span> host like <span class="string">'10.0.%'</span>\G;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/26.png?raw=true" alt=""></figure></p>
<h2 id="插入"><a href="#插入" class="headerlink" title="插入"></a>插入</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; update tb1 <span class="built_in">set</span> id=5 <span class="built_in">where</span> name = <span class="string">'docker'</span>;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/27.png?raw=true" alt=""></figure></p>
<h2 id="清空表"><a href="#清空表" class="headerlink" title="清空表"></a>清空表</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; truncate table yanyi.tb1;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;yanyi是库名，tb1是表名，清空以后表还在。</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/28.png?raw=true" alt=""></figure></p>
<h2 id="删除表"><a href="#删除表" class="headerlink" title="删除表"></a>删除表</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; delete from tb1 <span class="built_in">where</span> name=<span class="string">'redis'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这是删除表中指定的行</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/29.png?raw=true" alt=""></figure></p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; drop table tb1;</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这是删除整个表</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/30.png?raw=true" alt=""></figure></p>
<h2 id="删除数据库"><a href="#删除数据库" class="headerlink" title="删除数据库"></a>删除数据库</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; drop database yanyi;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/31.png?raw=true" alt=""></figure></p>
<h2 id="修复表"><a href="#修复表" class="headerlink" title="修复表"></a>修复表</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; repair table discuz.pre_forum_post;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/32.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以上就是一些常用的 mysql相关操作。还有个知识点，在 shell 的命令下去执行 mysql 的操作</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi mysql -e "show tables"</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;-e 前面的 mysql 指的是库的名字， -e 选项后面双引号括起来的就是 mysql 的命令。 </p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/33.png?raw=true" alt=""></figure></p>
<h2 id="授权超级用户"><a href="#授权超级用户" class="headerlink" title="授权超级用户"></a>授权超级用户</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">grant all privileges on *.* to <span class="string">'tangnanbing'</span>@<span class="string">'%'</span> identified by <span class="string">'1qaz@WSX'</span> with grant option;</div></pre></td></tr></table></figure>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/11.  mysql登录/">mysql登录</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql服务启动时，不仅会监听 IP:Port，还会监听一个socket。我们安装的mysql是监听在/tmp/mysql.sock。如果php是在本地，那么php和mysql通信可以通过socket通信，如果是远程，就需要通过tcp/ip来通信了。在Linux命令行下，可以通过如下方法来连接mysql服务器。</p>
<h2 id="tcp-ip的方式"><a href="#tcp-ip的方式" class="headerlink" title="tcp/ip的方式"></a>tcp/ip的方式</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># /usr/local/mysql/bin/mysql -uroot -h 127.0.0.1</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这样就连接上了，如果默认mysql的root用户密码为空，就不用加-p选项，不过最好还是设置一个密码。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># /usr/local/mysql/bin/mysqladmin -uroot password 'yanyi'</span></div></pre></td></tr></table></figure>
<p>`&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;只是第一次设置密码的时候可以这样，再次设置时，就需要先输入之前的root密码了。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># /usr/local/mysql/bin/mysqladmin -uroot -pyanyi password '123456'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;还有个问题，每次敲命令都是用的绝对路径，这样很繁琐，两个办法可以解决。第一个办法是设置alias，第二个办法是设置PATH</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># alias mysql=/usr/local/mysql/bin/mysql</span></div><div class="line">[root@lamp ~]<span class="comment"># alias mysqladmin=/usr/local/mysql/bin/mysqladmin</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果要永久生效，需要把两个alias放到 <code>.bashrc</code> 里面。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># vim .bashrc</span></div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E7%99%BB%E5%BD%95/01.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;另外就是设置PATH</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># vim /etc/profile.d/path.sh</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;加入</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">export</span> PATH=<span class="variable">$PATH</span>:/usr/<span class="built_in">local</span>/mysql/bin</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;保存后执行</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># source /etc/profile.d/path.sh</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;当给mysql设置密码后再去连接，就需要加上-p选项了</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi -h127.0.0.1</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;其中-h指定ip，如果是远程机器，则-h后面跟远程服务器ip，默认port是3306，如果是其他端口，需要用-P来定义</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi -h127.0.0.1 -P3306</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;另外有时候还需要mysql授权客户机，要不无法登录</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -pyanyi</span></div><div class="line"></div><div class="line">mysql&gt; grant all on *.* to <span class="string">'root'</span>@<span class="string">'192.168.0.99'</span> identified by <span class="string">'112233'</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;all就是所有权限。<em>.</em>前边的*表示所有的库，后边的*表示所有的表，这里就是所有的库和所有的表。’root’@’192.168.99’ root表示授权给root，192.168.0.99是客户端ip，授权哪个客户端IP。<code>identifined by</code> 后边跟的密码</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;查看</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; use mysql</div><div class="line">mysql&gt; selct * from user <span class="built_in">where</span> host=<span class="string">'192.168.0.99'</span>\G;</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E7%99%BB%E5%BD%95/02.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;再次连接</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -h192.168.0.99 -p112233</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里-p选项跟的密码是授权设置的密码，不是root的密码。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;登录以后查看一下当前登录用户</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql&gt; select user();</div></pre></td></tr></table></figure>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/mysql%E7%99%BB%E5%BD%95/03.png?raw=true" alt=""></figure></p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;用户是 root@192.168.0.99</p>
<h2 id="socket方式"><a href="#socket方式" class="headerlink" title="socket方式"></a>socket方式</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这种只适合连接本机的mysql：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@lamp ~]<span class="comment"># mysql -uroot -S /tmp/mysql.sock -pyanyi</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里的-S可以省略掉。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/10. mysql5.7 root密码更改/">mysql5.7root密码更改</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;mysql5.7root有默认密码，必须重设密码后，才能进行mysql的操作，以下是设置root密码的步骤</p>
<h2 id="一、查看默认密码"><a href="#一、查看默认密码" class="headerlink" title="一、查看默认密码"></a>一、查看默认密码</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@localhost src]<span class="comment"># cat /root/.mysql_secret</span></div><div class="line"><span class="comment"># The random password set for the root userat Fri Jan 10 20:00:34 2014 (local time): aJqZsA2m</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里的aJqZsA2m就是生成的root随机密码。</p>
<h2 id="二、登录mysql"><a href="#二、登录mysql" class="headerlink" title="二、登录mysql"></a>二、登录mysql</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@localhost src]<span class="comment"># mysql -u root -p</span></div><div class="line">Enter password:</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;输入上面的密码aJqZsA2m登录，如果没有把mysql的路径加到PATH里，那就用绝对路径。<code>mysql -u root -p</code> 还可以写成 <code>mysql -uroot -paJqZsA2m</code>。</p>
<h2 id="三、更改密码"><a href="#三、更改密码" class="headerlink" title="三、更改密码"></a>三、更改密码</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">mysql&gt; SET PASSWORD FOR <span class="string">'root'</span>@localhost = PASSWORD（<span class="string">'123456'</span>）；</div><div class="line">Query OK, 0 rows affected (0.17 sec)</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;至此，就成功的修改了密码。</p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MySQL/1. Mysql安装/">MySQL安装</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MySQL/">MySQL</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;平时安装mysql都是源码包安装的，但是由于它的编译需要很长的时间，所以建议使用二进制免编译包安装。可以到<a href="http://dev.mysql.com/downloads/" target="_blank" rel="external">MySQL官方网站</a>去下载，具体版本根据平台和需求而定，目前比较常用的为mysql-5.0/mysql-5.1，5.5和5.7版本虽然已经发布有段日子了，但是在线上跑服务器的还是少数。所以本文安装5.1的版本。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;安装以前一定要知道Linux系统是多少位。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">uname -i</div><div class="line">i386</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;i386其实就是32位，需要下载i686的包，如果结果为x86_64，则为64位。</p>
<h2 id="1、下载mysql到-usr-local-src"><a href="#1、下载mysql到-usr-local-src" class="headerlink" title="1、下载mysql到/usr/local/src/"></a>1、下载mysql到/usr/local/src/</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line"><span class="built_in">cd</span> /usr/<span class="built_in">local</span>/src/</div><div class="line">wget http://syslab.comsenz.com/downloads/linux/mysql-5.1.40-linux-i686-icc-glibc23.tar.gz</div></pre></td></tr></table></figure>
<h2 id="2、解压"><a href="#2、解压" class="headerlink" title="2、解压"></a>2、解压</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost src]<span class="comment"># tar zxvf /usr/local/src/mysql-5.1.40-linux-i686-icc-glibc23.tar.gz</span></div></pre></td></tr></table></figure>
<h2 id="3、把解压完的数据移动到-usr-local-mysql"><a href="#3、把解压完的数据移动到-usr-local-mysql" class="headerlink" title="3、把解压完的数据移动到/usr/local/mysql"></a>3、把解压完的数据移动到/usr/local/mysql</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost src]<span class="comment"># mv mysql-5.1.40-linux-i686-icc-glibc23 /usr/local/mysql</span></div></pre></td></tr></table></figure>
<h2 id="4、建立mysql用户"><a href="#4、建立mysql用户" class="headerlink" title="4、建立mysql用户"></a>4、建立mysql用户</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost src]<span class="comment"># useradd -s /sbin/nologin mysql</span></div></pre></td></tr></table></figure>
<h2 id="5、初始化数据库"><a href="#5、初始化数据库" class="headerlink" title="5、初始化数据库"></a>5、初始化数据库</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@localhost src]<span class="comment"># cd /usr/local/mysql</span></div><div class="line">[root@localhost mysql]<span class="comment"># mkdir -p /data/mysql ; chown -R mysql:mysql /data/mysql</span></div><div class="line">[root@localhost mysql]<span class="comment"># ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; <code>--user</code> 定义数据库的所属主， <code>--datadir</code> 定义数据库安装到哪里，建议放到大空间的分区上，这个目录需要自行创建。这一步很关键，如果看到两个“OK”，说明执行正确，否则请仔细查看错误信息。</p>
<h3 id="遇到错误1"><a href="#遇到错误1" class="headerlink" title="遇到错误1"></a>遇到错误1</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">./bin/mysqld: error <span class="keyword">while</span> loading shared librarues:libstdc++.so.5:cannot open shared object file:</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;解决办法</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">yum install -y compat-libstdc++-33</div></pre></td></tr></table></figure>
<h3 id="遇到错误2"><a href="#遇到错误2" class="headerlink" title="遇到错误2"></a>遇到错误2</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">./scripts/mysql_install_db: ./bin/my_print_defaults: /lib/ld-linux.so.2:bad ELF interpreter: NO such file or directory</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这是因为，系统版本和mysql版本不一致。比如Linux系统是32位，mysql安装包是64位。所以解决办法是重新下载合适的安装包。</p>
<h3 id="依赖包的安装"><a href="#依赖包的安装" class="headerlink" title="依赖包的安装"></a>依赖包的安装</h3><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">yum -y install make gcc-c++ cmake bison-devel ncurses-devel libaio libaio-devel perl-Data-Dumper net-tools</div></pre></td></tr></table></figure>
<h2 id="6、拷贝配置文件"><a href="#6、拷贝配置文件" class="headerlink" title="6、拷贝配置文件"></a>6、拷贝配置文件</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost mysql]<span class="comment"># cp support-files/my-large.cnf /etc/my.cnf</span></div></pre></td></tr></table></figure>
<h2 id="7、拷贝启动脚本文件并修改其属性"><a href="#7、拷贝启动脚本文件并修改其属性" class="headerlink" title="7、拷贝启动脚本文件并修改其属性"></a>7、拷贝启动脚本文件并修改其属性</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div></pre></td><td class="code"><pre><div class="line">[root@localhost mysql]<span class="comment"># cp support-files/mysql.server /etc/init.d/mysqld</span></div><div class="line">[root@localhost mysql]<span class="comment"># chmod 755 /etc/init.d/mysqld</span></div></pre></td></tr></table></figure>
<h2 id="8、修改启动脚本"><a href="#8、修改启动脚本" class="headerlink" title="8、修改启动脚本"></a>8、修改启动脚本</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost mysql]<span class="comment"># vim /etc/init.d/mysqld</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;需要修改的地方有 <code>datadir=/data/mysql</code> （之前初始化数据库时定义的目录）</p>
<h2 id="9、把启动脚本加入系统服务项，设定开机启动并启动mysql"><a href="#9、把启动脚本加入系统服务项，设定开机启动并启动mysql" class="headerlink" title="9、把启动脚本加入系统服务项，设定开机启动并启动mysql"></a>9、把启动脚本加入系统服务项，设定开机启动并启动mysql</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line">[root@localhost mysql]<span class="comment"># chkconfig --add mysqld</span></div><div class="line">[root@localhost mysql]<span class="comment"># chkconfig mysqld on</span></div><div class="line">[root@localhost mysql]<span class="comment"># service mysqld start</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;如果启动不了，请到 <code>/data/mysql/</code> 下查看错误日志，这个日志通常是 <code>主机名.err</code>。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;检查mysql是否启动：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">[root@localhost mysql]<span class="comment"># ps aux |grep mysqld</span></div></pre></td></tr></table></figure>
	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MongoDB/9. MongoDB PHP7/">MongoDB PHP7</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MongoDB/">MongoDB</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;本文教程只适合在 PHP7 的环境，如果是 PHP5 环境，可以参阅 <a href="https://hcldirgit.github.io/2017/08/27/MongoDB/8.%20MongoDB%20PHP/" target="_blank" rel="external">MongoDB PHP</a></p>
<h2 id="PHP7-Mongdb-扩展安装"><a href="#PHP7-Mongdb-扩展安装" class="headerlink" title="PHP7 Mongdb 扩展安装"></a>PHP7 Mongdb 扩展安装</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用 pecl 命令来安装：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">$ /usr/<span class="built_in">local</span>/php7/bin/pecl install mongodb</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行成功后，会输出以下结果：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">……</div><div class="line">Build process completed successfully</div><div class="line">Installing <span class="string">'/usr/local/php7/lib/php/extensions/no-debug-non-zts-20151012/mongodb.so'</span></div><div class="line">install ok: channel://pecl.php.net/mongodb-1.1.7</div><div class="line">configuration option <span class="string">"php_ini"</span> is not <span class="built_in">set</span> to php.ini location</div><div class="line">You should add <span class="string">"extension=mongodb.so"</span> to php.ini</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;接下来打开 php.ini 文件，添加 <strong>extension=mongodb.so</strong> 配置。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;可以直接执行以下命令来添加。</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">$ <span class="built_in">echo</span> <span class="string">"extension=mongodb.so"</span> &gt;&gt; `/usr/<span class="built_in">local</span>/php7/bin/php --ini | grep <span class="string">"Loaded Configuration"</span> | sed -e <span class="string">"s|.*:\s*||"</span>`</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;<strong>注意</strong>：<em>以上执行的命令中 php7 的安装目录为 /usr/local/php7/，如果你安装在其他目录，需要相应修改 pecl 与 php 命令的路径。</em></p>
<h2 id="Mongodb-使用"><a href="#Mongodb-使用" class="headerlink" title="Mongodb 使用"></a>Mongodb 使用</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;PHP7 连接 MongoDB 语法如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line"><span class="variable">$manager</span> = new MongoDB\Driver\Manager(<span class="string">"mongodb://localhost:27017"</span>);</div></pre></td></tr></table></figure>
<h3 id="插入数据"><a href="#插入数据" class="headerlink" title="插入数据"></a>插入数据</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;将 name 为”菜鸟教程” 的数据插入到 test 数据库的 runoob 集合中。</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$bulk = <span class="keyword">new</span> MongoDB\Driver\BulkWrite;</div><div class="line">$document = [<span class="string">'_id'</span> =&gt; <span class="keyword">new</span> MongoDB\BSON\ObjectID, <span class="string">'name'</span> =&gt; <span class="string">'菜鸟教程'</span>];</div><div class="line"> </div><div class="line">$_id= $bulk-&gt;insert($document);</div><div class="line"> </div><div class="line">var_dump($_id);</div><div class="line"> </div><div class="line">$manager = <span class="keyword">new</span> MongoDB\Driver\Manager(<span class="string">"mongodb://localhost:27017"</span>); </div><div class="line">$writeConcern = <span class="keyword">new</span> MongoDB\Driver\WriteConcern(MongoDB\Driver\WriteConcern::MAJORITY, <span class="number">1000</span>);</div><div class="line">$result = $manager-&gt;executeBulkWrite(<span class="string">'test.runoob'</span>, $bulk, $writeConcern);</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<h3 id="读取数据"><a href="#读取数据" class="headerlink" title="读取数据"></a>读取数据</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;这里将三个网址数据插入到 test 数据库的 sites 集合，并读取迭代出来：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$manager = <span class="keyword">new</span> MongoDB\Driver\Manager(<span class="string">"mongodb://localhost:27017"</span>); </div><div class="line"></div><div class="line"><span class="comment">// 插入数据</span></div><div class="line">$bulk = <span class="keyword">new</span> MongoDB\Driver\BulkWrite;</div><div class="line">$bulk-&gt;insert([<span class="string">'x'</span> =&gt; <span class="number">1</span>, <span class="string">'name'</span>=&gt;<span class="string">'菜鸟教程'</span>, <span class="string">'url'</span> =&gt; <span class="string">'http://www.runoob.com'</span>]);</div><div class="line">$bulk-&gt;insert([<span class="string">'x'</span> =&gt; <span class="number">2</span>, <span class="string">'name'</span>=&gt;<span class="string">'Google'</span>, <span class="string">'url'</span> =&gt; <span class="string">'http://www.google.com'</span>]);</div><div class="line">$bulk-&gt;insert([<span class="string">'x'</span> =&gt; <span class="number">3</span>, <span class="string">'name'</span>=&gt;<span class="string">'taobao'</span>, <span class="string">'url'</span> =&gt; <span class="string">'http://www.taobao.com'</span>]);</div><div class="line">$manager-&gt;executeBulkWrite(<span class="string">'test.sites'</span>, $bulk);</div><div class="line"></div><div class="line">$filter = [<span class="string">'x'</span> =&gt; [<span class="string">'$gt'</span> =&gt; <span class="number">1</span>]];</div><div class="line">$options = [</div><div class="line">    <span class="string">'projection'</span> =&gt; [<span class="string">'_id'</span> =&gt; <span class="number">0</span>],</div><div class="line">    <span class="string">'sort'</span> =&gt; [<span class="string">'x'</span> =&gt; <span class="number">-1</span>],</div><div class="line">];</div><div class="line"></div><div class="line"><span class="comment">// 查询数据</span></div><div class="line">$query = <span class="keyword">new</span> MongoDB\Driver\Query($filter, $options);</div><div class="line">$cursor = $manager-&gt;executeQuery(<span class="string">'test.sites'</span>, $query);</div><div class="line"></div><div class="line"><span class="keyword">foreach</span> ($cursor <span class="keyword">as</span> $document) &#123;</div><div class="line">    print_r($document);</div><div class="line">&#125;</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;输出结果为：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line">stdClass Object</div><div class="line">(</div><div class="line">    [x] =&gt; 3</div><div class="line">    [name] =&gt; taobao</div><div class="line">    [url] =&gt; http://www.taobao.com</div><div class="line">)</div><div class="line">stdClass Object</div><div class="line">(</div><div class="line">    [x] =&gt; 2</div><div class="line">    [name] =&gt; Google</div><div class="line">    [url] =&gt; http://www.google.com</div><div class="line">)</div></pre></td></tr></table></figure>
<h3 id="更新数据"><a href="#更新数据" class="headerlink" title="更新数据"></a>更新数据</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;接下来我们将更新 test 数据库 sites 集合中 x 为 2 的数据：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$bulk = <span class="keyword">new</span> MongoDB\Driver\BulkWrite;</div><div class="line">$bulk-&gt;update(</div><div class="line">    [<span class="string">'x'</span> =&gt; <span class="number">2</span>],</div><div class="line">    [<span class="string">'$set'</span> =&gt; [<span class="string">'name'</span> =&gt; <span class="string">'菜鸟工具'</span>, <span class="string">'url'</span> =&gt; <span class="string">'tool.runoob.com'</span>]],</div><div class="line">    [<span class="string">'multi'</span> =&gt; <span class="keyword">false</span>, <span class="string">'upsert'</span> =&gt; <span class="keyword">false</span>]</div><div class="line">);</div><div class="line"></div><div class="line">$manager = <span class="keyword">new</span> MongoDB\Driver\Manager(<span class="string">"mongodb://localhost:27017"</span>); </div><div class="line">$writeConcern = <span class="keyword">new</span> MongoDB\Driver\WriteConcern(MongoDB\Driver\WriteConcern::MAJORITY, <span class="number">1000</span>);</div><div class="line">$result = $manager-&gt;executeBulkWrite(<span class="string">'test.sites'</span>, $bulk, $writeConcern);</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;接下来我们使用 “db.sites.find()” 命令查看数据的变化，x 为 2 的数据已经变成了菜鸟工具：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/MongoDB%20PHP7/01.jpeg?raw=true" alt=""></figure></p>
<h3 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h3><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以下实例删除了 x 为 1 和 x 为 2的数据，注意 limit 参数的区别：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$bulk = <span class="keyword">new</span> MongoDB\Driver\BulkWrite;</div><div class="line">$bulk-&gt;delete([<span class="string">'x'</span> =&gt; <span class="number">1</span>], [<span class="string">'limit'</span> =&gt; <span class="number">1</span>]); <span class="comment">// limit 为 1 时，删除第一条匹配数据</span></div><div class="line">$bulk-&gt;delete([<span class="string">'x'</span> =&gt; <span class="number">2</span>], [<span class="string">'limit'</span> =&gt; <span class="number">0</span>]); <span class="comment">// limit 为 0 时，删除所有匹配数据</span></div><div class="line"> </div><div class="line">$manager = <span class="keyword">new</span> MongoDB\Driver\Manager(<span class="string">"mongodb://localhost:27017"</span>); </div><div class="line">$writeConcern = <span class="keyword">new</span> MongoDB\Driver\WriteConcern(MongoDB\Driver\WriteConcern::MAJORITY, <span class="number">1000</span>);</div><div class="line">$result = $manager-&gt;executeBulkWrite(<span class="string">'test.sites'</span>, $bulk, $writeConcern);</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;更多使用方法请参考：<a href="http://php.net/manual/en/book.mongodb.php。" target="_blank" rel="external">http://php.net/manual/en/book.mongodb.php。</a></p>

	

	

</article>




	<article>
	
		<h1><a href="/2017/10/12/MongoDB/8. MongoDB PHP/">MongoDB PHP</a></h1>
	
	<div class="article__infos">
		<span class="article__date">2017-10-12</span><br />
		
		
			<span class="article__tags">
			  	<a class="article__tag-link" href="/tags/MongoDB/">MongoDB</a>
			</span>
		
	</div>

	

	
		<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在php中使用mongodb必须使用 mongodb 的 php驱动。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;MongoDB PHP在各平台上的安装及驱动包下载请查看:<a href="https://hcldirgit.github.io/2017/08/27/MongoDB/7.%20MongoDBPHP%20%E6%89%A9%E5%B1%95/" target="_blank" rel="external">PHP安装MongoDB扩展驱动</a></p>
<p>如果使用的是 PHP7，请参阅：<a href="https://hcldirgit.github.io/2017/08/27/MongoDB/9.%20MongoDB%20PHP7/" target="_blank" rel="external">MongoDB PHP7</a></p>
<h2 id="确保连接及选择一个数据库"><a href="#确保连接及选择一个数据库" class="headerlink" title="确保连接及选择一个数据库"></a>确保连接及选择一个数据库</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;为了确保正确连接，需要指定数据库名，如果数据库在mongoDB中不存在，mongoDB会自动创建</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;代码片段如下：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$m = <span class="keyword">new</span> MongoClient(); <span class="comment">// 连接默认主机和端口为：mongodb://localhost:27017</span></div><div class="line">$db = $m-&gt;test; <span class="comment">// 获取名称为 "test" 的数据库</span></div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<h2 id="创建集合"><a href="#创建集合" class="headerlink" title="创建集合"></a>创建集合</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;创建集合的代码片段如下：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$m = <span class="keyword">new</span> MongoClient(); <span class="comment">// 连接</span></div><div class="line">$db = $m-&gt;test; <span class="comment">// 获取名称为 "test" 的数据库</span></div><div class="line">$collection = $db-&gt;createCollection(<span class="string">"runoob"</span>);</div><div class="line"><span class="keyword">echo</span> <span class="string">"集合创建成功"</span>;</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行以上程序，输出结果如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">集合创建成功</div></pre></td></tr></table></figure>
<h2 id="插入文档"><a href="#插入文档" class="headerlink" title="插入文档"></a>插入文档</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;在mongoDB中使用 insert() 方法插入文档：</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;插入文档代码片段如下：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$m = <span class="keyword">new</span> MongoClient(); <span class="comment">// 连接到mongodb</span></div><div class="line">$db = $m-&gt;test; <span class="comment">// 选择一个数据库</span></div><div class="line">$collection = $db-&gt;runoob; <span class="comment">// 选择集合</span></div><div class="line">$document = <span class="keyword">array</span>( </div><div class="line">        <span class="string">"title"</span> =&gt; <span class="string">"MongoDB"</span>, </div><div class="line">        <span class="string">"description"</span> =&gt; <span class="string">"database"</span>, </div><div class="line">        <span class="string">"likes"</span> =&gt; <span class="number">100</span>,</div><div class="line">        <span class="string">"url"</span> =&gt; <span class="string">"http://www.runoob.com/mongodb/"</span>,</div><div class="line">        <span class="string">"by"</span>, <span class="string">"菜鸟教程"</span></div><div class="line">);</div><div class="line">$collection-&gt;insert($document);</div><div class="line"><span class="keyword">echo</span> <span class="string">"数据插入成功"</span>;</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行以上程序，输出结果如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">数据插入成功</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后在 mongo 客户端使用 db.runoob.find().pretty(); 命令查看数据：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/MongoDB%20PHP/01.jpeg?raw=true" alt=""></figure></p>
<h2 id="查找文档"><a href="#查找文档" class="headerlink" title="查找文档"></a>查找文档</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用find() 方法来读取集合中的文档。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;读取使用文档的代码片段如下：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$m = <span class="keyword">new</span> MongoClient(); <span class="comment">// 连接到mongodb</span></div><div class="line">$db = $m-&gt;test; <span class="comment">// 选择一个数据库</span></div><div class="line">$collection = $db-&gt;runoob; <span class="comment">// 选择集合</span></div><div class="line"></div><div class="line">$cursor = $collection-&gt;find();</div><div class="line"><span class="comment">// 迭代显示文档标题</span></div><div class="line"><span class="keyword">foreach</span> ($cursor <span class="keyword">as</span> $document) &#123;</div><div class="line">    <span class="keyword">echo</span> $document[<span class="string">"title"</span>] . <span class="string">"\n"</span>;</div><div class="line">&#125;</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行以上程序，输出结果如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">MongoDB</div></pre></td></tr></table></figure>
<h2 id="更新文档"><a href="#更新文档" class="headerlink" title="更新文档"></a>更新文档</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用 update() 方法来更新文档。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以下实例将更新文档中的标题为’ MongoDB 教程’， 代码片段如下：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div></pre></td><td class="code"><pre><div class="line">&lt;pre&gt;</div><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$m = <span class="keyword">new</span> MongoClient(); <span class="comment">// 连接到mongodb</span></div><div class="line">$db = $m-&gt;test; <span class="comment">// 选择一个数据库</span></div><div class="line">$collection = $db-&gt;runoob; <span class="comment">// 选择集合</span></div><div class="line"><span class="comment">// 更新文档</span></div><div class="line">$collection-&gt;update(<span class="keyword">array</span>(<span class="string">"title"</span>=&gt;<span class="string">"MongoDB"</span>), <span class="keyword">array</span>(<span class="string">'$set'</span>=&gt;<span class="keyword">array</span>(<span class="string">"title"</span>=&gt;<span class="string">"MongoDB 教程"</span>)));</div><div class="line"><span class="comment">// 显示更新后的文档</span></div><div class="line">$cursor = $collection-&gt;find();</div><div class="line"><span class="comment">// 循环显示文档标题</span></div><div class="line"><span class="keyword">foreach</span> ($cursor <span class="keyword">as</span> $document) &#123;</div><div class="line">    <span class="keyword">echo</span> $document[<span class="string">"title"</span>] . <span class="string">"\n"</span>;</div><div class="line">&#125;</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;执行以上程序，输出结果如下：</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">MongoDB 教程</div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;然后在 mongo 客户端使用 db.runoob.find().pretty(); 命令查看数据：</p>
<p><figure class="figure"><img src="https://github.com/hcldirgit/image/blob/master/MongoDB%20PHP/02.jpeg?raw=true" alt=""></figure></p>
<h2 id="删除文档"><a href="#删除文档" class="headerlink" title="删除文档"></a>删除文档</h2><p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;使用 remove() 方法来删除文档。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;以下实例中将移除 ‘title’ 为 ‘MongoDB 教程’ 的一条数据记录。， 代码片段如下：</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&lt;?php</span></div><div class="line">$m = <span class="keyword">new</span> MongoClient(); <span class="comment">// 连接到mongodb</span></div><div class="line">$db = $m-&gt;test; <span class="comment">// 选择一个数据库</span></div><div class="line">$collection = $db-&gt;runoob; <span class="comment">// 选择集合</span></div><div class="line"></div><div class="line"><span class="comment">// 移除文档</span></div><div class="line">$collection-&gt;remove(<span class="keyword">array</span>(<span class="string">"title"</span>=&gt;<span class="string">"MongoDB 教程"</span>), <span class="keyword">array</span>(<span class="string">"justOne"</span> =&gt; <span class="keyword">true</span>));</div><div class="line"></div><div class="line"><span class="comment">// 显示可用文档数据</span></div><div class="line">$cursor = $collection-&gt;find();</div><div class="line"><span class="keyword">foreach</span> ($cursor <span class="keyword">as</span> $document) &#123;</div><div class="line">    <span class="keyword">echo</span> $document[<span class="string">"title"</span>] . <span class="string">"\n"</span>;</div><div class="line">&#125;</div><div class="line"><span class="meta">?&gt;</span></div></pre></td></tr></table></figure>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;除了以上实例外，在php中还可以使用findOne(), save(), limit(), skip(), sort()等方法来操作Mongodb数据库。</p>
<p>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;更多的操作方法可以参考 Mongodb 核心类：<a href="http://php.net/manual/zh/mongo.core.php。" target="_blank" rel="external">http://php.net/manual/zh/mongo.core.php。</a></p>

	

	

</article>





	<span class="different-posts">📖 <a href="/page/31">more posts</a> 📖</span>



	</main>

	<footer class="footer">
	<div class="footer-content">
		
	      <div class="footer__element">
	<p>Hi there, <br />welcome to my Blog glad you found it. Have a look around, will you?</p>
</div>

	    
	      <div class="footer__element">
	<h5>Check out</h5>
	<ul class="footer-links">
		<li class="footer-links__link"><a href="/archives">Archive</a></li>
		
		  <li class="footer-links__link"><a href="/atom.xml">RSS</a></li>
	    
		<li class="footer-links__link"><a href="/about">about page</a></li>
		<li class="footer-links__link"><a href="/tags">Tags</a></li>
		<li class="footer-links__link"><a href="/categories">Categories</a></li>
	</ul>
</div>

	    

		<div class="footer-credit">
			<span>© 2017 失落的乐章 | Powered by <a href="https://hexo.io/">Hexo</a> | Theme <a href="https://github.com/HoverBaum/meilidu-hexo">MeiliDu</a></span>
		</div>

	</div>


</footer>



</body>

</html>
